import pandas as pd
import numpy as np

df = pd.read_excel(r'C:\Users\86157\Desktop\work\zhzh\数据\test_data\苗圃19.10.10-10.21.xls')

df1 = df.groupby('date',as_index = False).mean()

#定义NO2AQI函数
def NO2_cut(x):
    if x<80:
        return x*1.25
    else:
        return x/2+60
#计算NO2AQI
list_NO2 = []
for i_NO2 in df1['NO2']:
    t_NO2 = NO2_cut(i_NO2)
    list_NO2.append(t_NO2)
# list_NO2
#转化成dataframe
df_NO2=pd.DataFrame(list_NO2, columns = ['NO2AQI'])
#定义O3AQI计算函数
def O3_cut(y):
    if y < 100:
        return y/2
    elif y < 160:
        return (5*y-200)/6
    elif y < 215:
        return 0.91*y-45.4
    elif y < 265:
        return y-65
    else:
        return 0.91*y+150
#计算O3AQI
list_O3 = []
for i_O3 in df1['O3']:
    t_O3 = O3_cut(i_O3)
    list_O3.append(t_O3)
# list_O3
#转化成dataframe
df_O3=pd.DataFrame(list_O3, columns = ['O3AQI'])

#计算PM2.5AQI
def PM25_cut(z):
    if z < 35:
        return z*10/7
    elif z < 75:
        return 1.25*z+6.25
    elif z < 115:
        return 1.43*z-14.3
    elif z < 350:
        return z+50
    else:
        return (2*z+500)/3
list_PM25 = []
for i_PM25 in df1['PM2.5']:
    t_PM25 = PM25_cut(i_PM25)
    list_PM25.append(t_PM25)
# list_PM25
#转化成dataframe
df_PM25=pd.DataFrame(list_PM25, columns = ['PM2.5AQI'])

#计算PM10
def PM10_cut(p):
    if p < 50:
        return p
    elif p < 350:
        return p/2+25
    elif p < 420:
        return 1.43*p-300
    elif p < 500:
        return 1.25*p-225
    else:
        return p-100
list_PM10 = []
for i_PM10 in df1['PM10']:
    t_PM10 = PM10_cut(i_PM10)
    list_PM10.append(t_PM10)
# list_PM10
#转化成dataframe
df_PM10=pd.DataFrame(list_PM10, columns = ['PM10AQI'])
# df1=df1.reset_index(drop=True)
df2 = pd.concat([df1,df_NO2,df_O3,df_PM25,df_PM10],axis=1)#,df_O3,df_PM25,df_PM10
# df2 = combine_as_data_location([df1,df_NO2,df_O3,df_PM25])

#计算NO2分指数
list1_NO2 = []
for j_NO2 in df1['NO2']:
    m_NO2 = j_NO2/40
    list1_NO2.append(m_NO2)
# list1_NO2
#转化成dataframe
df2_NO2=pd.DataFrame(list1_NO2, columns = ['NO2分指数'])

#计算O3分指数
list1_O3 = []
for j_O3 in df1['O3']:
    m_O3 = j_O3/160
    list1_O3.append(m_O3)
# list1_O3
#转化成dataframe
df2_O3=pd.DataFrame(list1_O3, columns = ['O3分指数'])

#计算PM2.5分指数
list1_PM25 = []
for j_PM25 in df1['PM2.5']:
    m_PM25 = j_PM25/35
    list1_PM25.append(m_PM25)
# list1_PM25
#转化成dataframe
df2_PM25=pd.DataFrame(list1_PM25, columns = ['PM2.5分指数'])

#计算PM10分指数
list1_PM10 = []
for j_PM10 in df1['PM10']:
    m_PM10 = j_PM10/70
    list1_PM10.append(m_PM10)
# list1_PM10
#转化成dataframe
df2_PM10=pd.DataFrame(list1_PM10, columns = ['PM10分指数'])

#合并到输出表
# #df1=df1.reset_index(drop=True)
df3 = pd.concat([df2,df2_NO2,df2_O3,df2_PM25,df2_PM10],axis=1)#,df_O3,df_PM25,df_PM10
#计算综合指数
df3_zonghe = df3['NO2分指数']+df3['O3分指数']+df3['PM2.5分指数']+df3['PM10分指数']
df3_zh = pd.DataFrame(df3_zonghe, columns = ['综合指数'])
df4 = pd.concat([df3, df3_zh], axis = 1)

#输出日报表
# df4.to_excel(r'C:\Users\Administrator\Desktop\work\zhzh\金牛区站点监测\数据分析报告191023.xlsx')


#计算O3八小时平均值
#计算1-8，2-9...17-0点的平均值取其中最大值。

#获取date列的唯一值
df_date = df['date'].drop_duplicates()
df6 = df.groupby('date',as_index = False)
#创建一个空的dataframe和循环里的合并
avg_O3_8 = pd.DataFrame()
for date_0 in df_date:
    df6_1=df6.get_group(date_0)#获取groupby后的一个组的dataframe
    sp = df6_1['O3']        #获取O3列用来计算滑动平均
    N=8                     #定义滑动平均为8小时
    n=np.ones(8)
    weights=n/N            #设置权重，这里为全部相同
    sma=np.convolve(weights,sp,'full')[N-1:-N+1]#计算滑动平均[N-1:N+1]控制输出长度
    avg_8=pd.DataFrame(sma, columns = [date_0])
    avg_O3_8 = pd.concat([avg_O3_8,avg_8], axis = 1)


#计算90百分位数
#将一天内的数据排序取排90%的值
df5 = df.groupby('date',as_index = False)
df5.apply(lambda x: x.sort_values('O3', ascending = True).quantile(q=0.90))


# np.percentile(avg_O3_8['2019-10-10'],90)#
per_90 = avg_O3_8.quantile(q=0.90)
per_90_1 = pd.DataFrame(per_90)
per_90_1 = per_90_1.reset_index(drop=True)
per_90_1
# df4.reset_index(drop=True)
daily_data = pd.concat([df4,per_90_1], axis = 1)
print(daily_data)



